package com.studentmanagement.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.studentmanagement.model.Enrollment;
import com.studentmanagement.util.PageBean;

public class EnrollmentDAO extends BaseDAO<Enrollment> {
    
    // 添加选课记录
    public int addEnrollment(Enrollment enrollment) throws SQLException {
        String sql = "INSERT INTO enrollments (enrollment_id, student_id, course_id, enrollment_date, grade) " +
                     "VALUES (enrollment_seq.NEXTVAL, ?, ?, ?, ?)";
        
        return update(sql, enrollment.getStudentId(), enrollment.getCourseId(), 
                     enrollment.getEnrollmentDate(), enrollment.getGrade());
    }
    
    // 更新选课记录
    public int updateEnrollment(Enrollment enrollment) throws SQLException {
        String sql = "UPDATE enrollments SET student_id = ?, course_id = ?, enrollment_date = ?, grade = ? " +
                     "WHERE enrollment_id = ?";
        
        return update(sql, enrollment.getStudentId(), enrollment.getCourseId(), 
                     enrollment.getEnrollmentDate(), enrollment.getGrade(), 
                     enrollment.getEnrollmentId());
    }
    
    // 删除选课记录
    public int deleteEnrollment(int enrollmentId) throws SQLException {
        String sql = "DELETE FROM enrollments WHERE enrollment_id = ?";
        return update(sql, enrollmentId);
    }
    
    // 根据ID查询选课记录
    public Enrollment getEnrollmentById(int enrollmentId) throws SQLException {
        String sql = "SELECT e.*, s.name as student_name, c.course_name " +
                     "FROM enrollments e " +
                     "JOIN students s ON e.student_id = s.student_id " +
                     "JOIN courses c ON e.course_id = c.course_id " +
                     "WHERE e.enrollment_id = ?";
        
        return queryForObject(sql, new RowMapper<Enrollment>() {
            @Override
            public Enrollment mapRow(ResultSet rs) throws SQLException {
                Enrollment enrollment = new Enrollment();
                enrollment.setEnrollmentId(rs.getInt("enrollment_id"));
                enrollment.setStudentId(rs.getInt("student_id"));
                enrollment.setCourseId(rs.getInt("course_id"));
                enrollment.setEnrollmentDate(rs.getDate("enrollment_date"));
                enrollment.setGrade(rs.getString("grade"));
                enrollment.setStudentName(rs.getString("student_name"));
                enrollment.setCourseName(rs.getString("course_name"));
                return enrollment;
            }
        }, enrollmentId);
    }
    
    // 分页查询选课记录
    public List<Enrollment> getEnrollmentsByPage(PageBean<Enrollment> pageBean) throws SQLException {
        // 构建三层子查询SQL
        String sql = "SELECT * FROM (" +
                     "  SELECT a.*, ROWNUM rn FROM (" +
                     "    SELECT e.*, s.name as student_name, c.course_name " +
                     "    FROM enrollments e " +
                     "    JOIN students s ON e.student_id = s.student_id " +
                     "    JOIN courses c ON e.course_id = c.course_id";
        
        // 添加排序条件
        if (pageBean.getSortField() != null && !pageBean.getSortField().isEmpty()) {
            sql += " ORDER BY e." + pageBean.getSortField() + " " + pageBean.getSortOrder();
        } else {
            sql += " ORDER BY e.enrollment_id ASC";
        }
        
        sql += "  ) a WHERE ROWNUM <= ?" +
               ") WHERE rn > ?";
        
        int end = pageBean.getPageNumber() * pageBean.getPageSize();
        int start = (pageBean.getPageNumber() - 1) * pageBean.getPageSize();
        
        return queryForList(sql, new RowMapper<Enrollment>() {
            @Override
            public Enrollment mapRow(ResultSet rs) throws SQLException {
                Enrollment enrollment = new Enrollment();
                enrollment.setEnrollmentId(rs.getInt("enrollment_id"));
                enrollment.setStudentId(rs.getInt("student_id"));
                enrollment.setCourseId(rs.getInt("course_id"));
                enrollment.setEnrollmentDate(rs.getDate("enrollment_date"));
                enrollment.setGrade(rs.getString("grade"));
                enrollment.setStudentName(rs.getString("student_name"));
                enrollment.setCourseName(rs.getString("course_name"));
                return enrollment;
            }
        }, end, start);
    }
    
    // 获取选课记录总数
    public int getEnrollmentCount() throws SQLException {
        String sql = "SELECT COUNT(*) FROM enrollments";
        return getCount(sql);
    }
    
    // 根据学生ID查询选课记录
    public List<Enrollment> getEnrollmentsByStudentId(int studentId) throws SQLException {
        String sql = "SELECT e.*, s.name as student_name, c.course_name " +
                     "FROM enrollments e " +
                     "JOIN students s ON e.student_id = s.student_id " +
                     "JOIN courses c ON e.course_id = c.course_id " +
                     "WHERE e.student_id = ?";
        
        return queryForList(sql, new RowMapper<Enrollment>() {
            @Override
            public Enrollment mapRow(ResultSet rs) throws SQLException {
                Enrollment enrollment = new Enrollment();
                enrollment.setEnrollmentId(rs.getInt("enrollment_id"));
                enrollment.setStudentId(rs.getInt("student_id"));
                enrollment.setCourseId(rs.getInt("course_id"));
                enrollment.setEnrollmentDate(rs.getDate("enrollment_date"));
                enrollment.setGrade(rs.getString("grade"));
                enrollment.setStudentName(rs.getString("student_name"));
                enrollment.setCourseName(rs.getString("course_name"));
                return enrollment;
            }
        }, studentId);
    }
    
    // 根据课程ID查询选课记录
    public List<Enrollment> getEnrollmentsByCourseId(int courseId) throws SQLException {
        String sql = "SELECT e.*, s.name as student_name, c.course_name " +
                     "FROM enrollments e " +
                     "JOIN students s ON e.student_id = s.student_id " +
                     "JOIN courses c ON e.course_id = c.course_id " +
                     "WHERE e.course_id = ?";
        
        return queryForList(sql, new RowMapper<Enrollment>() {
            @Override
            public Enrollment mapRow(ResultSet rs) throws SQLException {
                Enrollment enrollment = new Enrollment();
                enrollment.setEnrollmentId(rs.getInt("enrollment_id"));
                enrollment.setStudentId(rs.getInt("student_id"));
                enrollment.setCourseId(rs.getInt("course_id"));
                enrollment.setEnrollmentDate(rs.getDate("enrollment_date"));
                enrollment.setGrade(rs.getString("grade"));
                enrollment.setStudentName(rs.getString("student_name"));
                enrollment.setCourseName(rs.getString("course_name"));
                return enrollment;
            }
        }, courseId);
    }
}

